﻿using EFCoreDemo.Entitys;
using EFCoreDemo.Entitys.Entitys;
using EFCoreDemo.IRepositorys;
using EFCoreDemo.IService;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace EFCoreDemo.Service
{
    public class ArticleService : BaseService<ArticleEntity>, IArticleService
    {
        private readonly ITagRepository _tagRepository;
        private readonly IArticleDetailRepository _articleDetailRepository;
        public ArticleService(IUnitOfWork unitOfWork, IArticelRepository articelRepository
            , ITagRepository tagRepository, IArticleDetailRepository articleDetailRepository)
             : base(unitOfWork, articelRepository)
        {
            _tagRepository = tagRepository;
            _articleDetailRepository = articleDetailRepository;
        }

        //public async Task<bool> AddArticle()
        //{   
        //    unitOfWork.BeginTransaction();
        //    await _tagRepository.InsertAsync(new TagEntity { Id = Guid.NewGuid().ToString("N"), Name = "标签A" });
        //    await unitOfWork.Repository<TagEntity>().InsertAsync(new TagEntity { Id = Guid.NewGuid().ToString("N"), Name = "标签A" });
        //    var id = Guid.NewGuid().ToString("N");
        //    await currentRepository.InsertAsync(new ArticleEntity { Id = id, Poster = "poster", Title = "Title", Summary = "Summary" });
        //    await _articleDetailRepository.InsertAsync(new ArticleDetailEntity { ArticleId = id, Id = Guid.NewGuid().ToString("N"), Content = "CONTENT" });
        //    await unitOfWork.CommitAsync();
        //    return true;
        //} 
        public Task<ArticleEntity> GetArticle(string id)
        {
            return currentRepository.GetEntity(m => m.Id == id);
        }

        public async Task<bool> AddArticle()
        {
            var id = Guid.NewGuid().ToString("N");
            List<ArticleTagEntity> tags = new List<ArticleTagEntity>
            {
                new ArticleTagEntity {Id=Guid.NewGuid().ToString("N"),ArticleId=id,TagId="03b3a2f06df14881bd17e5784c111ff5"},
                new ArticleTagEntity {Id=Guid.NewGuid().ToString("N"),ArticleId=id,TagId="167646ab5d814f4cb8f17564f30b1451"},
                new ArticleTagEntity {Id=Guid.NewGuid().ToString("N"),ArticleId=id,TagId="54bd2676991548eaad058c2d213c0278"}
            };
            unitOfWork.BeginTransaction();
            var entity = await currentRepository.InsertAsync(new ArticleEntity { Id = Guid.NewGuid().ToString("N"), UserId = "001", Poster = "poster", Title = "Title", Summary = "Summary" });
            await _articleDetailRepository.InsertAsync(new ArticleDetailEntity { ArticleId = id, Id = Guid.NewGuid().ToString("N"), Content = $"CONTENT:{Guid.NewGuid()}" });
            await unitOfWork.Repository<ArticleTagEntity>().BatchInserAsync(tags);
            await unitOfWork.CommitAsync();
            return true;
        }

        #region INNER JOIN 
        /// <summary>
        /// inner join Lambda 写法
        /// SELECT [u].[Id], [u].[Name], [a].[Title], [a].[Summary]
        /// FROM[Users] AS[u]
        /// INNER JOIN[Articles] AS[a] ON[u].[Id] = [a].[UserId] 
        /// </summary>
        /// <returns></returns> 
        public async Task<object> GetArticles()
        {
            var a = currentRepository.Query().ToList();
            Console.WriteLine(a);
            var articles = unitOfWork.Repository<UserEntity>().Query()
                                     .Join(currentRepository.Query(), a => a.Id, ad => ad.UserId,
                                        (a, ad) => new
                                        {
                                            a.Id,
                                            a.Name,
                                            ad.Title,
                                            ad.Summary
                                        });
            return await articles.ToListAsync();
        }

        /// <summary>
        ///inner join linq 写法 
        /// SELECT [a].[Id], [a].[Title], [a].[Summary], [a].[CreateTime], [a0].[Content]
        /// FROM[Articles] AS[a]
        /// INNER JOIN[ArticleDetails] AS[a0] ON[a].[Id] = [a0].[ArticleId]
        /// </summary>
        /// <returns></returns>
        public async Task<object> GetArticlesInnerJoin()
        {
            var articles = from a in currentRepository.Query()
                           join ad in unitOfWork.Repository<ArticleDetailEntity>().Query() on a.Id equals ad.ArticleId
                           select new
                           {
                               a.Id,
                               a.Title,
                               a.Summary,
                               a.CreateTime,
                               ad.Content
                           };
            return await articles.ToListAsync();
        }
        #endregion

        #region  LEFT JOIN  1 - 1  
        /// <summary>
        ///  LEFT JOIN  1 - n
        ///  lambda 写法
        /// </summary>
        /// <returns></returns>
        public async Task<object> GetArticlesLeftJoin2()
        {
            var articles = unitOfWork.Repository<UserEntity>().Query()
                                     .GroupJoin(currentRepository.Query(), a => a.Id, ad => ad.UserId,
                                        (a, ad) => new
                                        {
                                            a.Id,
                                            a.Name,
                                            ad
                                        })
                                        .SelectMany(m => m.ad.DefaultIfEmpty(), (a, ad) => new
                                        {
                                            a.Id,
                                            a.Name,
                                            ad.Title,
                                            ad.Summary
                                        });
            return await articles.ToListAsync();
        }

        /// <summary>
        ///  LEFT JOIN  1 - n
        ///  linq 写法
        /// </summary>
        /// <returns></returns>
        public async Task<object> GetArticlesLeftJoin()
        {
            var articles = from u in unitOfWork.Repository<UserEntity>().QueryWithNoLock()
                           from a in currentRepository.QueryWithNoLock().Where(m => m.UserId == u.Id).DefaultIfEmpty()
                           select new
                           {
                               u.Id,
                               u.Name,
                               a.Title,
                               a.Summary
                           };
            return await articles.ToListAsync();
        }
        #endregion

        #region  LEFT JOIN  n - n 
        /// <summary>
        /// linq 写法 
        /// SELECT[a].[Id], [a].[Title], [a].[Summary], [t].[TagName]
        /// AS[Name]
        /// FROM[Articles] AS[a]
        /// LEFT JOIN[ArticleTags] AS[a0] ON[a].[Id] = [a0].[ArticleId]
        /// LEFT JOIN[Tags] AS[t] ON[a0].[TagId] = [t].[Id]
        /// </summary>
        /// <returns></returns> 
        public async Task<object> GetArticlesLeftJoins2()
        {
            var atQuery = unitOfWork.Repository<ArticleTagEntity>().Query();
            var tQuery = unitOfWork.Repository<TagEntity>().Query();
            var articles = from a in currentRepository.Query()
                           join at in atQuery on a.Id equals at.ArticleId into attemp
                           from ats in attemp.DefaultIfEmpty()
                           join t in tQuery on ats.TagId equals t.Id into ttemp
                           from ts in ttemp.DefaultIfEmpty()
                           select new
                           {
                               a.Id,
                               a.Title,
                               a.Summary,
                               TagName = ts.Name
                           };
            var result = await articles.ToListAsync();
            return result.GroupBy(m => new { m.Id, m.Title, m.Summary })
                         .Select(m => new
                         {
                             m.Key.Id,
                             m.Key.Title,
                             m.Key.Summary,
                             Tags = m.Select(m => m.TagName)
                         });
        }

        // SELECT[a].[Id], [a].[Title], [a].[Summary], [t0].[TagName]
        // FROM[Articles] AS[a]
        // LEFT JOIN(
        //    SELECT[a0].[ArticleId], [a0].[TagId]
        //    FROM [ArticleTags] AS [a0]
        //    WHERE [a0].[CreateTime] < GETDATE()
        // ) AS[t] ON[a].[Id] = [t].[ArticleId]
        //        INNER JOIN[Tags] AS[t0] ON[t].[TagId] = [t0].[Id]


        // SELECT[a].[Id], [a].[Title], [a].[Summary], [t].[TagName]
        // FROM[Articles] AS[a]
        // LEFT JOIN[ArticleTags] AS[a0] ON[a].[Id] = [a0].[ArticleId]
        // INNER JOIN[Tags] AS[t] ON[a0].[TagId] = [t].[Id]
        // WHERE[a0].[CreateTime] < GETDATE()
        // 官方文档：https://learn.microsoft.com/zh-cn/ef/core/querying/complex-query-operators
        public async Task<object> GetArticlesLeftJoins()
        {
            Expression<Func<ArticleTagEntity, bool>> expression = m => m.CreateTime < DateTime.Now;
            var atQuery = unitOfWork.Repository<ArticleTagEntity>().Query();
            var tQuery = unitOfWork.Repository<TagEntity>().Query();
            var articles = from a in currentRepository.Query()
                           from at in atQuery.Where(m => m.ArticleId == a.Id).DefaultIfEmpty()
                           from t in tQuery.Where(m => m.Id == at.TagId)
                           where at.CreateTime < DateTime.Now
                           select new
                           {
                               a.Id,
                               a.Title,
                               a.Summary,
                               TagName = t.Name
                           };
            var result = await articles.ToListAsync();
            return result.GroupBy(m => new { m.Id, m.Title, m.Summary })
                         .Select(m => new
                         {
                             m.Key.Id,
                             m.Key.Title,
                             m.Key.Summary,
                             Tags = m.Select(m => m.TagName)
                         });
        } 
        #endregion
    }
}
